<?php
require_once("common.php"); 
require_once("constants.php"); 


function connect() {
        $conn = mysql_connect(DB_SERVER, SQL_LOGIN_ID, SQL_LOGIN_PASSWORD);
        if (!$conn) {
            die('Could not connect: ' . mysql_error());
        }
        mysql_select_db(SQL_DATABASE, $conn);   
        return $conn;   
}
    
function disconnect($conn) {
    mysql_close($conn);
}
    
//is concurrency issue real?
function executeInsertAndGetKey($sql) {
    $conn = connect();
    mysql_query("START TRANSACTION");
    $result = mysql_query($sql);
	log1($sql);
	if (mysql_error()) {
		log1("Error executing sql: \n" . $sql);
		log1(mysql_error());
		die();
	}
	 
    $key = null;
    if ($result) {      
        $key = mysql_insert_id();
		//log1("primary key:" . $key);
    }
    mysql_query("COMMIT");
    disconnect($conn);
    return $key;
}


function executeSQL($sql) {
    $conn = connect();
    $result = mysql_query($sql);
	log1($sql);
	if (mysql_error()) {
		log1("Error executing sql: \n" . $sql);
		log1(mysql_error());
		die();
	}
    //disconnect($conn);    
    return $result;
}   

function readTableStructure($tableName) {
    $conn = connect();
    $tableStructureQuery = "show full columns from " . $tableName;
    $result = mysql_query($tableStructureQuery);
    
    $counter = 0;
    while($row = mysql_fetch_array($result)) {
         $field = array (
            "Field" => $row['Field'],
            "Null" => $row['Null'],
            "Default" => $row['Default'],
            "Comment" => $row['Comment'],
            "Type" => $row['Type'] );
        $tableStructure[$counter++] = $field;       
    }       
     
     return;
}

function updateEmployee($selectedEmployee, $e_email , $e_phone_no ,$e_hours_per_day)  {
    $update_employee_sql = "update employee set e_email = '" . $e_email . "'," .
        "e_phone_no = '" . $e_phone_no . "', e_hours_per_day =" . $e_hours_per_day .
        " where e_employee_id=" . $selectedEmployee;
    $result = executeSQL($update_employee_sql);
    
    return $result; 
}

function deleteEmployee($selectedEmployee)  {
    
    $delete_employee_holidays_sql = "delete from holiday  where h_employee_id=" . $selectedEmployee;
    $result = executeSQL($delete_employee_holidays_sql);
        
    $delete_employee_sql = "delete from employee  where e_employee_id=" . $selectedEmployee;
    $result = executeSQL($delete_employee_sql);
    
    return $result; 
}
    
function insertEmployee($e_title , $e_firstname , $e_surname , $e_email , $e_phone ,$e_hours) {
    $insert_employee_sql = "insert into employee" . 
    "(e_title, e_firstname, e_surname, e_email, e_phone_no, e_hours_per_day)" .
    "values ('" . $e_title . "','" . $e_firstname . "','".  $e_surname . "','" . $e_email . "'," . $e_phone . "," . $e_hours. ")";
    
    return executeInsertAndGetKey($insert_employee_sql);
    
}

function getBankHolidays() {
    $select_bank_holidays_sql  = "select h_date from holiday where h_bank_holiday ='Y'";
    $result = executeSQL($select_bank_holidays_sql);
    $i = 0;
    while ($row = mysql_fetch_array($result)) {
         $dates[$i++] = strtotime($row['h_date']);
    }
    return $dates;
   
}

function isBankHoliday($workDay) {
	$check_bank_holiday_sql = "select count(*) from holiday where h_date = $workDay and h_bank_holiday = 'Y'"; 
	 $result = executeSQL($check_bank_holiday_sql);
	$row = mysql_fetch_array($result);
	if ($row) {
		return $row[0];
	}
}

function getEmployeeHolidays($selectedEmployee) {
    $select_employee_holidays_sql  = "select h_date from holiday where h_employee_id ='" .
        $selectedEmployee . "'";
    $result = executeSQL($select_employee_holidays_sql);
    $i = 0;
    while ($row = mysql_fetch_array($result)) {
         $dates[$i++] = strtotime($row['h_date']);
    }
    return  $dates;
   
}


function setEmployeeHolidays($selectedEmployee, $date_array) {
    $error = false;
    $conn = connect();
    
    $delete_before_update = "delete from holiday where h_employee_id=" . "'" .$selectedEmployee .  "'";
    $result = mysql_query($delete_before_update);
    if (mysql_error()) $error = true;
    
    foreach($date_array as $dat) {
        $insert_date = "insert into holiday (h_employee_id, h_date,  h_bank_holiday) ".
        "values ('" . $selectedEmployee . "',  '" . $dat . "' , 'N')" ;
        $result = mysql_query($insert_date);
        
        if (mysql_error()) $error = true;
    } 
        
    if($error) {
        mysql_query("ROLLBACK"); 
        disconnect($conn);  
        return 0;
    }
    else {
        mysql_query("COMMIT"); 
        disconnect($conn);  
        return 1;
    }
}

function setBankHolidays($date_array) {
    $error = false;
    $conn = connect();
    $rs1 = mysql_query("START TRANSACTION");
    
    $delete_before_update = "delete from holiday where h_bank_holiday='Y'";
    $result = mysql_query($delete_before_update);
    if (mysql_error()) $error = true;
    
    foreach($date_array as $dat) {
        $insert_date = "insert into holiday (h_employee_id, h_date,  h_bank_holiday) ".
        "values ('" . ALL_EMPLOYEES_ID . "',  '" . $dat . "' , 'Y')" ;
        $result = mysql_query($insert_date);
        
        if (mysql_error()) $error = true;
    } 
        
    if($error) {
        mysql_query("ROLLBACK"); 
        disconnect($conn);  
        return 0;
    }
    else {
        mysql_query("COMMIT"); 
        disconnect($conn);  
        return 1;
    }
}

function getAllEmployees() {
    $select_employees_sql  = "select e_employee_id, e_title, e_firstname, e_surname, e_email, e_phone_no, e_hours_per_day from employee order by e_surname";
    $result = executeSQL($select_employees_sql);
    $i = 0;
	 
    while ($row = mysql_fetch_array($result)) {
        $employee = array (
                    "e_employee_id" => $row['e_employee_id'],
                    "e_title" => $row['e_title'],
                    "e_firstname" => $row['e_firstname'],
                    "e_surname" => $row['e_surname'],
                    "e_email" => $row['e_email'],
                    "e_phone_no" => $row['e_phone_no'],
                    "e_hours_per_day" => $row['e_hours_per_day']
                 ); 
         $employees[$i++] = $employee;
    }
   	return $employees;
    
    
}

function getAllShippingOptions() {
	$conn = connect();

	$select_shipping_options_sql = "select ds_service_id, ds_service_name, ds_max_width, ds_max_height, ds_max_length, ds_guaranteed, ds_delivery_time, ds_status from delivery_services";
	$result = mysql_query($select_shipping_options_sql);
	$i = 0;
	$services = null; while ($row = mysql_fetch_array($result)) {
		$services[$i++] = $row;
	}
	for ($j = 0; $j < $i; $j++) {
		$row = $services[$j];
		$select_shipping_prices_sql = "select dc_weight_limit, dc_price from delivery_charges where dc_delivery_service_id = " .
		$row['ds_service_id'];
		$result = mysql_query($select_shipping_prices_sql);
		$prices = null; while ($row1 = mysql_fetch_array($result)) {
			$prices[$row1['dc_weight_limit']] = $row1['dc_price'];
		}
		$shippingOption = array (
			"ds_service_id" => $row['ds_service_id'],
			"ds_service_name" => $row['ds_service_name'],

			"ds_max_width" => $row['ds_max_width'],
			"ds_max_height" => $row['ds_max_height'],
			"ds_max_length" => $row['ds_max_length'],
			"ds_guaranteed" => $row['ds_guaranteed'],
			"ds_delivery_time" => $row['ds_delivery_time'],
			"ds_status" => $row['ds_status'],
			"price" => $prices
		);
		$shippingOptions[$j] = $shippingOption;
	}
	return $shippingOptions;
}


function updateShippingOption($ds_service_id, $ds_service_name , $ds_max_width,
                                $ds_max_height, $ds_max_length, $ds_guaranteed, $ds_delivery_time, $ds_status, $prices)  {
    ksort($prices);
    $error = false;
    $conn = connect();
    mysql_query("START TRANSACTION");
    
    $update_shipping_option_sql = "update delivery_services set ds_service_name = '$ds_service_name'," .
    " ds_max_height = $ds_max_height , ds_max_width = $ds_max_width," .
    "ds_max_length = $ds_max_length, ds_guaranteed ='$ds_guaranteed', ds_delivery_time = $ds_delivery_time, " .
    "ds_status = '$ds_status' where ds_service_id=$ds_service_id";
    
    
    
    $result = mysql_query($update_shipping_option_sql) or die(mysql_error());
    if (mysql_error()) $error = true;
    $delete_prices_sql = "delete from delivery_charges where dc_delivery_service_id=" . $ds_service_id;
    $result = mysql_query($delete_prices_sql)or die(mysql_error());
    if (mysql_error()) $error = true;
    foreach ($prices as $dc_weight_limit => $dc_price) {
    	$insert_price_category_sql = "insert into delivery_charges" .
    	"(dc_delivery_service_id, dc_weight_limit, dc_price)" .
    	"values ('" . $ds_service_id . "','" . $dc_weight_limit . "','" . $dc_price . "')";
    	$result = mysql_query($insert_price_category_sql) or die(mysql_error());
    	if (mysql_error())
    		$error = true;
    }
    
    if($error) {
        mysql_query("ROLLBACK"); 
        disconnect($conn);  
        return 0;
    }
    else {
        mysql_query("COMMIT"); 
        disconnect($conn);  
        return 1;
    }
}

function deleteShippingOption($selectedShippingOption)  {
    $error = false;
    $conn = connect();
    mysql_query("START TRANSACTION");
    
    $delete_shipping_sql = "delete from delivery_charges where dc_delivery_service_id=" . $selectedShippingOption;
    $result = mysql_query($delete_shipping_sql);
    if (mysql_error()) $error = true;   
    
    $delete_shipping_sql = "delete from delivery_services where ds_service_id=" . $selectedShippingOption;
    $result = mysql_query($delete_shipping_sql);
    if (mysql_error()) $error = true;
    
    if($error) {
        mysql_query("ROLLBACK");
        disconnect($conn);   
        return 0;
    }
    else {
        mysql_query("COMMIT"); 
        disconnect($conn);  
        return 1;
    }   
}
    
function insertShippingOption($ds_service_name , $ds_max_width,
        $ds_max_height, $ds_max_length, $ds_guaranteed, $ds_delivery_time, $ds_status, $prices) {

    ksort($prices);
    $error = false;
    $conn = connect();
    mysql_query("START TRANSACTION");
         
    $insert_shipping_option_sql = "insert into delivery_services" . 
        "(ds_service_name, ds_max_width, ds_max_height, ds_max_length, ds_guaranteed, ds_delivery_time, ds_status)" .
        "values ('" . $ds_service_name . "'," . "," . $ds_max_width . "," .  
        $ds_max_height . "," . $ds_max_length . ",'" . $ds_guaranteed . "'," . $ds_delivery_time. ",'". $ds_status. "')";
    $result = mysql_query($insert_shipping_option_sql) or die(mysql_error());
    if (mysql_error()) $error = true;
    
    $select_new_ID = "select ds_service_id from delivery_services where ds_service_name = '" . $ds_service_name . "'";
    $result = mysql_query($select_new_ID) or die(mysql_error());
    if (mysql_error()) $error = true;   
    $row = mysql_fetch_assoc($result);
    $ds_service_id = $row['ds_service_id'];
    
    foreach ($prices as $dc_weight_limit => $dc_price) {
        $insert_price_category_sql = "insert into delivery_charges" .
        "(dc_delivery_service_id, dc_weight_limit, dc_price)" .
        "values ('" . $ds_service_id . "','" . $dc_weight_limit . "','".  $dc_price . "')";
        $result = mysql_query($insert_price_category_sql) or die(mysql_error());;
        if (mysql_error()) $error = true;           
    }
    if($error) {
        mysql_query("ROLLBACK"); 
        disconnect($conn);
        return 0;
    }
    else {
        mysql_query("COMMIT"); 
        disconnect($conn);
        return 1;
    }   
}


function getAllGarments() {
    $select_garments_sql  = 
        "select g_garment_id, 
            g_name,
            g_description,
            g_image_thumbnail,
            g_image_closeup,
            g_customer_rating,
            g_keywords,
            g_price,
            g_hours_to_make,
            g_est_fabric_required
        from garment";
		 
    $result = executeSQL($select_garments_sql);
    $i = 0;
    while ($row = mysql_fetch_array($result)) {
        $garment = array (
	        "g_garment_id" => $row['g_garment_id'],
	        "g_name" => $row['g_name'],
	        "g_description" => $row['g_description'],
	        "g_image_thumbnail" => $row['g_image_thumbnail'],
	        "g_image_closeup" => $row['g_image_closeup'],
	        "g_customer_rating" => $row['g_customer_rating'],
	        "g_keywords" => $row['g_keywords'],
	        "g_price" => $row['g_price'],
	        "g_hours_to_make" => $row['g_hours_to_make'],
	        "g_est_fabric_required" => $row['g_est_fabric_required']                    
         ); 
         $garments[$row['g_garment_id']] = $garment;
    }
  	return  $garments;
  
}


function getGarmentsByKeyword($searchKey) {
	
    $garments = getAllGarments();
    $matchingGarmentIDs = null;
    foreach($garments as $garment)  {
        $keywords = explode(KEYWORD_SEPARATOR, $garment[g_keywords]);
        $i = 0;
        foreach ($keywords as $keyword) {
            if (strcasecmp($searchKey, $keyword) == 0) {
                $matchingGarmentIDs[$i++]= $garment['g_garment_id'];
            } 
        }
    }
}


function getGarment($garmentID) {
    $select_garments_sql  = 
        "select g_garment_id, 
                g_name,
                g_description,
                g_image_thumbnail,
                g_image_closeup,
                g_customer_rating,
                g_keywords,
                g_price,
                g_hours_to_make,
                g_est_fabric_required
        from garment where g_garment_id=" . $garmentID;
			 
    $result = executeSQL($select_garments_sql);
    $i = 0;
    $row = mysql_fetch_array($result);
    if ($row) {
        $garment = array (
                    "g_garment_id" => $row['g_garment_id'],
                    "g_name" => $row['g_name'],
                    "g_description" => $row['g_description'],
                    "g_image_thumbnail" => $row['g_image_thumbnail'],
                    "g_image_closeup" => $row['g_image_closeup'],
                    "g_customer_rating" => $row['g_customer_rating'],
                    "g_keywords" => $row['g_keywords'],
                    "g_price" => $row['g_price'],
                    "g_hours_to_make" => $row['g_hours_to_make'],
                    "g_est_fabric_required" => $row['g_est_fabric_required']                    
                 ); 
        return $garment;
    }
    else {
        return null;
    }
}

function getAllFabrics() {
    $select_fabrics_sql  = 
        "select f_id, 
                f_name,
                f_description,
                f_type,
                f_color,
                f_pattern,
                f_customer_rating,
                f_image,
                f_price_per_m
        from fabric";
			 
    $result = executeSQL($select_fabrics_sql);
    $i = 0;
    while ($row = mysql_fetch_array($result)) {
        $fabric = array (
                    "f_id" => $row['f_id'],
                    "f_name" => $row['f_name'],
                    "f_description" => $row['f_description'],
                    "f_type" => $row['f_type'],
                    "f_color" => $row['f_color'],
                    "f_pattern" => $row['f_pattern'],
                    "f_customer_rating" => $row['f_customer_rating'],
                    "f_image" => $row['f_image'],
                    "f_price_per_m" => $row['f_price_per_m']        
                 ); 
         $fabrics[$row['f_id']] = $fabric;
    }
 	return $fabrics;
    
}

function getFabric($fabricID) {
    $select_fabrics_sql  = 
        "select f_id, 
                f_name,
                f_description,
                f_type,
                f_color,
                f_pattern,
                f_customer_rating,
                f_image,
                f_price_per_m
        from fabric where f_id=" . $fabricID;
	 
    $result = executeSQL($select_fabrics_sql);
     
     $row = mysql_fetch_array($result);
	 
	 	 
     if ($row) {
        $fabric = array (
                    "f_id" => $row['f_id'],
                    "f_name" => $row['f_name'],
                    "f_description" => $row['f_description'],
                    "f_type" => $row['f_type'],
                    "f_color" => $row['f_color'],
                    "f_pattern" => $row['f_pattern'],
                    "f_customer_rating" => $row['f_customer_rating'],
                    "f_image" => $row['f_image'],
                    "f_price_per_m" => $row['f_price_per_m']        
                 ); 
        return $fabric;
    }
    else {
        return null;
    }
}

function insertAddress($a_contact_name, $a_company_name, $a_address_type, $a_house_no, $a_flat_no, $a_house_name, $a_street, 
    $a_address_line_1, $a_address_line_2, $a_town_city, $a_postcode, $a_county, $a_email, $a_phone) {

	
    $insert_address_sql = "insert into address" . 
        "(a_contact_name, a_company_name, a_address_type, a_house_no, a_flat_no, a_house_name, a_street, 
        a_address_line_1, a_address_line_2, a_town_city, a_postcode, a_county, a_email, a_phone) 
        values ('$a_contact_name', '$a_company_name', '$a_address_type', '$a_house_no', '$a_flat_no', '$a_house_name', '$a_street', 
        '$a_address_line_1', '$a_address_line_2', '$a_town_city', '$a_postcode', '$a_county', '$a_email', '$a_phone')";

   return executeInsertAndGetKey($insert_address_sql);
  
}

function insertUnregisteredCustomer() {
/*
c_psp_buyer_id
c_customer_id
c_password
c_firstname
c_surname
c_title
c_date_of_birth
c_billing_address_id
c_home_address_id
c_shipping_address_id
c_email
c_phone_no
c_photo
*/
}

function updateRegisteredCustomer() {
    
}

function insertSize($s_name, $s_shoulder, $s_chest, $s_bust, $s_waist, $s_hips, $s_back_waist, $s_leg, $s_height) {

	$insert_size_sql = "INSERT INTO size ( s_size_id ,  s_name ,  s_shoulder ,  s_chest ,  s_bust , 
					 s_waist ,  s_hips ,  s_back_waist ,  s_leg ,  s_height )
	           		VALUES ('$s_name' , $s_shoulder , $s_chest , $s_bust , 
					$s_waist , $s_hips , $s_back_waist , $s_leg , $s_height)";
	return executeInsertAndGetKey($insert_size_sql);
}

function insertOrder($o_order_id,
		$o_customer_id,
		$o_registered_customer_id,
		$o_shipping_address,
		$o_billing_address,
		$o_order_date,
		$o_order_time,
		$o_due_date,
		$o_dispatch_date,
		$o_total_price,
		$o_order_status,
		$o_payment_status,
		$o_shipping_cost_charged,
		$o_shipping_service) {
			
	$insert_order_sql = "insert into customer_order (o_order_id,
													o_customer_id,
													o_registered_customer_id,
													o_shipping_address,
													o_billing_address,
													o_order_date,
													o_order_time,
													o_due_date,
													o_dispatch_date,
													o_total_price,
													o_order_status,
													o_payment_status,
													o_shipping_cost_charged,
													o_shipping_service)
							values ('$o_order_id',
									'$o_customer_id',
									'$o_registered_customer_id',
									$o_shipping_address,
									$o_billing_address,
									'$o_order_date',
									'$o_order_time',
									'$o_due_date',
									'$o_dispatch_date',
									$o_total_price,
									'$o_order_status',
									'$o_payment_status',
									$o_shipping_cost_charged,
									'$o_shipping_service'
									)";
	
	 						
	return executeSQL($insert_order_sql);																						
}

function updateOrderStatus($o_order_id, $o_order_status, $o_payment_status) {
	$update_status_sql = "update customer_order set  o_order_status = '$o_order_status', o_payment_status = '$o_payment_status' 
	where o_order_id = '$o_order_id'";
	return executeSQL($update_status_sql);
}

function insertOrderItem($oi_garment_id, $oi_fabric_id, $oi_order_id, $oi_size_id, $oi_ready_to_ship, $oi_price_displayed, $oi_real_cost) {

	$insert_order_item_sql = "insert into ordered_item (oi_garment_id,
															oi_fabric_id,
															oi_order_id,
															oi_size_id,
															oi_ready_to_ship,
															oi_price_displayed,
															oi_real_cost)
									values ($oi_garment_id,
											$oi_fabric_id,
											$oi_order_id,
											$oi_size_id,
											'$oi_ready_to_ship',
											'$oi_price_displayed',
											'$oi_real_cost')";
	return executeInsertAndGetKey($insert_order_item_sql);
}


 
function allocateWork($employeeID, $workDay, $orderItemID,  $timeTaken, $hoursPerDay) {
			//$result = mysql_query("call allocateWork($employeeID , $workDay , $orderItemID)");
			//log1( $result);//$row = mysql_fetch_array($result);//log1( $row);$error = false;

	$check_bank_holiday_sql = "select count(*)  from holiday where h_employee_id = $employeeID and h_bank_holiday = 'N'";

	$result = executeSQL($check_bank_holiday_sql);
	$row = mysql_fetch_array($result);
	if ($row[0] == 1) {
		return 0;
	}

	$allocation_sofar_sql = "select sum(t_hours_of_work)  from tasklist where t_employee_id = $employeeID and t_date_assigned = '$workDay'";
	$result = executeSQL($allocation_sofar_sql);
	$row = mysql_fetch_array($result);
	$occupiedHours = $row[0];
	if ($occupiedHours == null || $occupiedHours == "") {
		$occupiedHours = 0;
	}
	//log1(" hoursPerDay = $hoursPerDay");
	//log1(" occupiedHours = $occupiedHours");
	//log1(" timeTaken = $timeTaken");

	if ($hoursPerDay - $occupiedHours < $timeTaken) {
		return 0;
	}

	$insert_task_sql = "insert into tasklist (t_employee_id, t_ordered_item_id, t_date_assigned, t_hours_of_work)
	values ($employeeID, $orderItemID, '$workDay', $timeTaken)";

	$result = executeSQL($insert_task_sql);
	return 1;
}


function getShippableOrdersSQL() {
	$select_ready_sql = "SELECT DISTINCT customer_order.o_order_id
						FROM tasklist,ordered_item,customer_order
						WHERE customer_order.o_order_id = ordered_item.oi_order_id AND
							ordered_item.oi_item_id =  tasklist.t_ordered_item_id AND
							tasklist.t_date_assigned >=  DATE_SUB(CURDATE(), INTERVAL 14 DAY)  AND
							tasklist.t_date_assigned < CURDATE() AND
							(customer_order.o_order_status LIKE  'NEW' or 
							customer_order.o_order_status LIKE  'PROCESSING') AND
							o_payment_status like 'CHARGEABLE'	";
	return $select_ready_sql;
}
/*
function getShippableOrders() {
	$select_ready_sql = getShippableOrdersSQL();
	$result = executeSQL($select_ready_sql);
	 $i = 0;
    while ($row = mysql_fetch_array($result)) {
         $orderIDs[$i++] = $row['o_order_id'];
    }
	if ($i != 0) {
		return $orderIDs;
	}
	else {
		return null;	
	}	
}
*/
function getOrdersSQL($orderIDSelect) {
	$select_customer_details_sql = "SELECT distinct customer_order.o_order_id,
			address.a_contact_name,
			address.a_address_line_1,
			address.a_address_line_2,
			address.a_phone,
			address.a_email,
			customer_order.o_total_price,
			o_shipping_cost_charged,
			o_shipping_service,
			customer_order.o_customer_id,
			customer_order.o_payment_status,
			customer_order.o_order_status,
			customer_order.o_due_date,
			customer_order.o_order_date,
			customer_order.o_order_time,
			customer_order.o_dispatch_date			
		FROM
			customer_order ,
			address 
		WHERE
			
			customer_order.o_shipping_address =  address.a_address_id AND
			customer_order.o_order_id IN (" 
		. 
		$orderIDSelect		
		.")"
		. "ORDER BY customer_order.o_due_date, customer_order.o_order_id";	
	return $select_customer_details_sql;
	//customer_order.o_order_date >=  DATE_SUB(CURDATE(), INTERVAL 14 DAY)  AND	
	//add above to filter active orders only!
	//orders are active in a 14 day window, the time span in which delivery is guaranteed by mtm
	//include another sql for archived orders
}

function parseOrders($result) {
	$i = 0;
    while ($row = mysql_fetch_array($result)) {
        $orderDetails = array (
					"o_order_id" => $row['o_order_id'],
                    "a_contact_name" => $row['a_contact_name'],
                    "a_address_line_1" => $row['a_address_line_1'],
                    "a_address_line_2" => $row['a_address_line_2'],
                    "a_phone" => $row['a_phone'],
                    "a_email" => $row['a_email'],
                    "o_total_price" => $row['o_total_price'],
                    "o_shipping_cost_charged" => $row['o_shipping_cost_charged'],
                    "o_shipping_service" => $row['o_shipping_service'],
					"o_customer_id" => $row['o_customer_id'],
                    "o_payment_status" => $row['o_payment_status'],
                    "o_order_status" => $row['o_order_status'],
                    "o_due_date" => $row['o_due_date'],
                    "o_order_date" => $row['o_order_date'],
                    "o_order_time" => $row['o_order_time'],
					"o_order_id" => $row['o_order_id'],
                    "o_dispatch_date" => $row['o_dispatch_date']			
                 ); 
         $orders[$i++] = $orderDetails;
    }
	
	if (isset($orders)) {
		//log1("Returning orders:" . $orders);
   	return $orders;	
	}
	else {
		return null;
	}
}

function getShippableOrders() {
	$select_order_details_sql = getOrdersSQL(getShippableOrdersSQL());		
	$result = executeSQL($select_order_details_sql); 
   	return parseOrders($result);		
}

function getShippedOrders() {
	$select_shipped_SQL = "select o_order_id from customer_order 
		where o_order_status like 'DELIVERED' and o_payment_status not like 'CHARGED'";
	$select_order_details_sql = getOrdersSQL($select_shipped_SQL);		
	$result = executeSQL($select_order_details_sql); 
   	return parseOrders($result);		
}

function getChargedOrders() {
	$select_charged_SQL = "select o_order_id from customer_order 
		where o_payment_status like 'CHARGED'";
	$select_order_details_sql = getOrdersSQL($select_charged_SQL);		
	$result = executeSQL($select_order_details_sql); 
   	return parseOrders($result);		
}

function getAllOrders() {
	$select_all_SQL = "select o_order_id from customer_order";
	$select_order_details_sql = getOrdersSQL($select_all_SQL);		
	$result = executeSQL($select_order_details_sql); 
   	return parseOrders($result);		
}

function getDeliveryNoteData($orderID) {
	$select_delivery_data_sql =
		"SELECT
			garment.g_name,
			fabric.f_name,
			size.s_shoulder,
			size.s_chest,
			size.s_bust,
			size.s_hips,
			size.s_waist,
			size.s_back_waist,
			size.s_leg,
			size.s_height,
			ordered_item.oi_price_displayed,
			ordered_item.oi_item_id
		FROM
			garment ,
			fabric ,
			size ,
			customer_order ,
			ordered_item
		WHERE
			garment.g_garment_id =  ordered_item.oi_garment_id AND
			fabric.f_id =  ordered_item.oi_fabric_id AND
			size.s_size_id =  ordered_item.oi_size_id AND
			ordered_item.oi_order_id =  customer_order.o_order_id AND
			customer_order.o_order_id =  '$orderID'";

    $i = 0;
	$result = executeSQL($select_delivery_data_sql); 
	$deliveryData = null;
    while ($row = mysql_fetch_array($result)) {
        $orderedItemData = array (
					"g_name" => $row['g_name'],
                    "f_name" => $row['f_name'],
					
                    "s_shoulder" => $row['s_shoulder'],
                    "s_chest" => $row['s_chest'],
                    "s_bust" => $row['s_bust'],					
                    "s_hips" => $row['s_hips'],
                    "s_waist" => $row['s_waist'],
                    "s_back_waist" => $row['s_back_waist'],					
                    "s_leg" => $row['s_leg'],
                    "s_height" => $row['s_height'],
                    "oi_price_displayed" => $row['oi_price_displayed'],
                    "oi_item_id" => $row['oi_item_id']
                 ); 
         $deliveryData[$i++] = $orderedItemData;
    }
   
   	return $deliveryData;	
 
}	

function getTasksSQL($filterBy) {
	$tasksSQL = "SELECT
					customer_order.o_order_id,
					customer_order.o_due_date,
					tasklist.t_date_assigned,
					tasklist.t_employee_id,
					tasklist.t_hours_of_work,
					employee.e_firstname,
					employee.e_surname,
					ordered_item.oi_item_id,
					ordered_item.oi_garment_id,
					ordered_item.oi_fabric_id,
					size.s_height,
					size.s_leg,
					size.s_back_waist,
					size.s_hips,
					size.s_waist,
					size.s_bust,
					size.s_chest,
					size.s_shoulder
				FROM
					customer_order ,
					tasklist ,
					employee ,
					ordered_item ,
					size
				WHERE
					customer_order.o_order_id =  ordered_item.oi_order_id AND
					ordered_item.oi_item_id =  tasklist.t_ordered_item_id AND
					tasklist.t_employee_id =  employee.e_employee_id AND
					size.s_size_id =  ordered_item.oi_size_id AND"
					.
					$filterBy
					.
				" ORDER BY
					customer_order.o_order_id ASC,	
					ordered_item.oi_item_id ASC ";
	return $tasksSQL;
}

function parseTasks($result) {
	$i = 0;
	$tasks = null;
    while ($row = mysql_fetch_array($result)) {
        $taskDetails = array (
					"o_order_id" => $row['o_order_id'],
                    "o_due_date" => $row['o_due_date'],
                    "t_date_assigned" => $row['t_date_assigned'],
                    "t_employee_id" => $row['t_employee_id'],
                    "t_hours_of_work" => $row['t_hours_of_work'],
                    "e_firstname" => $row['e_firstname'],
                    "e_surname" => $row['e_surname'],
                    "oi_item_id" => $row['oi_item_id'],
                    "oi_garment_id" => $row['oi_garment_id'],
					"oi_fabric_id" => $row['oi_fabric_id'],
					
                    "s_shoulder" => $row['s_shoulder'],
                    "s_chest" => $row['s_chest'],
                    "s_bust" => $row['s_bust'],					
                    "s_hips" => $row['s_hips'],
                    "s_waist" => $row['s_waist'],
                    "s_back_waist" => $row['s_back_waist'],					
                    "s_leg" => $row['s_leg'],
                    "s_height" => $row['s_height']		
                 ); 
         $tasks[$i++] = $taskDetails;
    }
 
   	return $tasks;	
 
}

function getOutstandingTasks() {
	$where_condition = " tasklist.t_date_assigned >= CURDATE() ";
	$select_tasks_sql = getTasksSQL($where_condition);
	$result = executeSQL($select_tasks_sql);
	return parseTasks($result);
}

function getOutstandingTasksForEmployee($employeeID) {
	$where_condition = " tasklist.t_employee_id = '$employeeID' AND tasklist.t_date_assigned >= CURDATE() ";
	$select_tasks_sql = getTasksSQL($where_condition);	
	$result = executeSQL($select_tasks_sql);
	return parseTasks($result);
}

function getTasksByOrder($orderID) {
	$where_condition = " customer_order.o_order_id like '$orderID' ";
	$select_tasks_sql = getTasksSQL($where_condition);
	$result = executeSQL($select_tasks_sql);	
	return parseTasks($result);
}

function getTasksByDate($dateAssigned) {
	$where_condition = " tasklist.t_date_assigned = '$dateAssigned'  ";
	$select_tasks_sql = getTasksSQL($where_condition);
	$result = executeSQL($select_tasks_sql);	
	return parseTasks($result);
}

function getTodaysTasks() {
	$where_condition = " tasklist.t_date_assigned = CURDATE() ";
	$select_tasks_sql = getTasksSQL($where_condition);
	$result = executeSQL($select_tasks_sql);	
	return parseTasks($result);
}



function reassignTask($oi_item_id, $t_date_assigned,$t_employee_id, $t_hours_of_work) {
	
	$reassign_sql = "update tasklist set t_date_assigned = '$t_date_assigned',
										t_employee_id = $t_employee_id,
										t_hours_of_work = $t_hours_of_work
					 where t_ordered_item_id = $oi_item_id";	
	return executeSQL($reassign_sql);			
}	

function updateFabric($selectedFabric, $f_name ,$f_type, $f_description, $f_color, $f_pattern, $f_image, $f_price_per_m, $f_stock, $f_fabric_width, $f_supplier_id)  {
	$update_fabric_sql = "update fabric set f_name = '" . $f_name . "'," .
		"f_type = '" . $f_type . "', f_description ='" . $f_description .
		"', f_color ='" . $f_color .
		"', f_pattern ='" . $f_pattern .
		"', f_image ='" . $f_image .
		"', f_price_per_m ='" . $f_price_per_m .
		"', f_stock ='" . $f_stock .
		"', f_fabric_width ='" . $f_fabric_width .
		"', f_supplier_id ='" . $f_supplier_id .
		"' where f_id=" . $selectedFabric;
	$result = executeSQL($update_fabric_sql);
	
	return $result;	
}

function deleteFabric($selectedFabric)  {
	$delete_fabric_sql = "delete from fabric  where f_id=" . $selectedFabric;
	$result = executeSQL($delete_fabric_sql);
	
	return $result;	
}
	
function insertFabric($f_name ,$f_type, $f_description, $f_color, $f_pattern, $f_image, $f_price_per_m, $f_stock, $f_fabric_width, $f_supplier_id) {
	$insert_fabric_sql = "insert into fabric" . 
	"(f_name ,f_type, f_description, f_color, f_pattern, f_image, f_price_per_m, f_stock, f_fabric_width, f_supplier_id)" .
	"values ('" . $f_name . "','" . $f_type . "','".  $f_description . "','" . $f_color . "'," . $f_pattern . "," . $f_image . "," . $f_price_per_m . "," . $f_stock . "," . $f_fabric_width . "," . $f_supplier_id . ")";
	
	$result = executeSQL($insert_fabric_sql);
	return $result;
}

function getAllFabrics2() {
	$select_fabrics_sql  = "select f_id, f_name ,f_type, f_description, f_color, f_pattern, f_image, f_price_per_m, f_stock, f_fabric_width, f_supplier_id from fabric order by f_name";
	$result = executeSQL($select_fabrics_sql);
	$i = 0;
	while ($row = mysql_fetch_array($result)) {
		$fabric = array (
					"f_id" => $row['f_id'],
                 	"f_name" => $row['f_name'],
                 	"f_type" => $row['f_type'],
                 	"f_description" => $row['f_description'],
                 	"f_color" => $row['f_color'],
                 	"f_pattern" => $row['f_pattern'],
                 	"f_image" => $row['f_image'],
                 	"f_price_per_m" => $row['f_price_per_m'],
                 	"f_stock" => $row['f_stock'],
                 	"f_fabric_width" => $row['f_fabric_width'],
					"f_supplier_id" => $row['f_supplier_id']
                 );	
		 $fabrics[$i++] = $fabric;
	}
	if ($i != 0) {
		$_SESSION[FABRICS] = $fabrics;
	}
	
}

function updateGarment($selectedGarment, $g_name , $g_style ,$g_department, $g_description, $g_image_thumbnail, $g_image_closeup, $g_keywords, $g_price, $g_hours_to_make, $g_est_fabric_required)  {
	$update_garment_sql = "update garment set g_name = '" . $g_name . "'," .
		"g_style = '" . $g_style . "', g_department ='" . $g_department .
		"', g_description ='" . $g_description .
		"', g_image_thumbnail ='" . $g_image_thumbnail .
		"', g_image_closeup ='" . $g_image_closeup .
		"', g_keywords ='" . $g_keywords .
		"', g_price ='" . $g_price .
		"', g_hours_to_make ='" . $g_hours_to_make .
		"', g_est_fabric_required ='" . $g_est_fabric_required .
		"' where g_garment_id=" . $selectedGarment;
	$result = executeSQL($update_garment_sql);
	
	return $result;	
}

function deleteGarment($selectedGarment)  {
	$delete_garment_sql = "delete from garment  where g_garment_id=" . $selectedGarment;
	$result = executeSQL($delete_garment_sql);
	
	return $result;	
}
	
function insertGarment($g_name , $g_style ,$g_department, $g_description, $g_image_thumbnail, $g_image_closeup, $g_keywords, $g_price, $g_hours_to_make, $g_est_fabric_required) {
	$insert_garment_sql = "insert into garment" . 
	"(g_name , g_style ,g_department, g_description, g_image_thumbnail, g_image_closeup, g_keywords, g_price, g_hours_to_make, g_est_fabric_required)" .
	"values ('" . $g_name . "','" . $g_style . "','".  $g_department . "','" . $g_description . "'," . $g_image_thumbnail . "," . $g_image_closeup . "," . $g_keywords . "," . $g_price . "," . $g_hours_to_make . "," . $g_est_fabric_required . ")";
	
	$result = executeSQL($insert_garment_sql);
	return $result;
}

function getAllGarments2() {
	$select_garment_sql  = "select g_garment_id, g_name , g_style ,g_department, g_description, g_image_thumbnail, g_image_closeup, g_keywords, g_price, g_hours_to_make, g_est_fabric_required from garment order by g_name";
	$result = executeSQL($select_garment_sql);
	$i = 0;
	while ($row = mysql_fetch_array($result)) {
		$garment = array (
					"g_garment_id" => $row['g_garment_id'],
                 	"g_name" => $row['g_name'],
                 	"g_style" => $row['g_style'],
                 	"g_department" => $row['g_department'],
                 	"g_description" => $row['g_description'],
                 	"g_image_thumbnail" => $row['g_image_thumbnail'],
                 	"g_image_closeup" => $row['g_image_closeup'],
                 	"g_keywords" => $row['g_keywords'],
                 	"g_price" => $row['g_price'],
                 	"g_hours_to_make" => $row['g_hours_to_make'],
					"g_est_fabric_required" => $row['g_est_fabric_required']
                 );	
		 $garments[$i++] = $garment;
	}
	if ($i != 0) {
		$_SESSION[GARMENTS] = $garments;
	}
	
}

?>